﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using GSQ.BusinessEntities;
using GSQ.DataAccess.DataSetTableAdapters;

namespace GSQ.DataAccess
{
    public class ClientesDA
    {
        private CLIENTESTableAdapter Adapter = new CLIENTESTableAdapter();
        private V_CLIENTESTableAdapter vAdapter = new V_CLIENTESTableAdapter();
        private DataSet.CLIENTESDataTable entities;
        private DataSet.CLIENTESRow entity;

        public decimal Insert(CLIENTES value)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    Adapter.Connection = cnn;

                    entities = new DataSet.CLIENTESDataTable();

                    entity = entities.NewCLIENTESRow();

                    entity.ID = value.ID;
                    entity.CLIENTE = value.CLIENTE;
                    entity.NOMBRE_COMERCIAL = value.NOMBRE_COMERCIAL;
                    entity.DIRECCION = value.DIRECCION;
                    entity.ID_CIUDAD = value.ID_CIUDAD;
                    entity.ID_VENDEDOR = value.ID_VENDEDOR;
                    entity.RNC = value.RNC;
                    entity.TELEFONO = value.TELEFONO;
                    if (value.CELULAR != null)
                        entity.CELULAR = value.CELULAR;
                    if (value.FAX != null)
                        entity.FAX = value.FAX;
                    if (value.OTRO != null)
                        entity.OTRO = value.OTRO;
                    if (value.EMAIL != null)
                        entity.EMAIL = value.EMAIL;
                    entity.FECHA = value.FECHA;
                    if (value.OBSERVACIONES != null)
                        entity.OBSERVACIONES = value.OBSERVACIONES;
                    if (value.LIMITE_CREDITO != null)
                        entity.LIMITE_CREDITO = value.LIMITE_CREDITO.Value;
                    entity.ACTIVO = value.ACTIVO;
                    entity.ID_CATEGORIA = value.ID_CATEGORIA;
                    if (value.ID_ZONA != null)
                    {
                        entity.ID_ZONA = value.ID_ZONA.Value;
                    }
                    if (value.ID_RUTA != null)
                    {
                        entity.ID_RUTA = value.ID_RUTA.Value;
                    }
                    entity.ID_FORMA_PAGO = value.ID_FORMA_PAGO;
                    entity.IMPUESTOS = value.IMPUESTOS;

                    entities.AddCLIENTESRow(entity);
                    Adapter.Update(entities);

                    value.ID = entity.ID;
                    return value.ID;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public decimal Update(CLIENTES value)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.Adapter.Connection = cnn;

                    entities = Adapter.GetDataByID(value.ID);
                    entity = entities.FirstOrDefault();


                    entity.CLIENTE = value.CLIENTE;
                    entity.NOMBRE_COMERCIAL = value.NOMBRE_COMERCIAL;
                    entity.DIRECCION = value.DIRECCION;
                    entity.ID_CIUDAD = value.ID_CIUDAD;
                    entity.ID_VENDEDOR = value.ID_VENDEDOR;
                    entity.RNC = value.RNC;
                    entity.TELEFONO = value.TELEFONO;
                    if (value.CELULAR != null)
                        entity.CELULAR = value.CELULAR;
                    if (value.FAX != null)
                        entity.FAX = value.FAX;
                    if (value.OTRO != null)
                        entity.OTRO = value.OTRO;
                    if (value.EMAIL != null)
                        entity.EMAIL = value.EMAIL;
                    entity.FECHA = value.FECHA;
                    if (value.OBSERVACIONES != null && value.OBSERVACIONES != string.Empty)
                        entity.OBSERVACIONES = value.OBSERVACIONES;
                    if (value.LIMITE_CREDITO != null)
                        entity.LIMITE_CREDITO = value.LIMITE_CREDITO.Value;
                    entity.ACTIVO = value.ACTIVO;
                    entity.ID_CATEGORIA = value.ID_CATEGORIA;
                    if (value.ID_ZONA != null)
                    {
                        entity.ID_ZONA = value.ID_ZONA.Value;
                    }
                    if (value.ID_RUTA != null)
                    {
                        entity.ID_RUTA = value.ID_RUTA.Value;
                    }
                    entity.ID_FORMA_PAGO = value.ID_FORMA_PAGO;
                    entity.IMPUESTOS = value.IMPUESTOS;

                    this.Adapter.Update(entities);

                    return entity.ID;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public CLIENTES GetById(decimal id)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.vAdapter.Connection = cnn;

                    CLIENTES cliente;

                    DataSet.V_CLIENTESRow rw = this.vAdapter.GetDataByID(id).FirstOrDefault();

                    if (rw != null)
                    {
                        cliente = GetEntity(rw);
                    }
                    else
                        cliente = null;

                    return cliente;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public List<CLIENTES> GetAll()
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.vAdapter.Connection = cnn;

                    List<CLIENTES> lst = new List<CLIENTES>();
                    foreach (DataSet.V_CLIENTESRow rw in this.vAdapter.GetData())
                    {
                        lst.Add(GetEntity(rw));
                    }
                    cnn.Close();
                    return lst;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public List<CLIENTES> GetByCliente(string value)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.vAdapter.Connection = cnn;

                    List<CLIENTES> lst = new List<CLIENTES>();
                    foreach (DataSet.V_CLIENTESRow rw in this.vAdapter.GetDataByCliente(value.Trim()))
                    {
                        lst.Add(GetEntity(rw));
                    }
                    cnn.Close();
                    return lst;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public CLIENTES GetByRnc(string value)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.vAdapter.Connection = cnn;

                    CLIENTES cliente;

                    DataSet.V_CLIENTESRow rw = this.vAdapter.GetDataByRnc(value.Trim()).FirstOrDefault();

                    if (rw != null)
                    {
                        cliente = GetEntity(rw);
                    }
                    else
                        cliente = null;

                    return cliente;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public List<CLIENTES> GetByCiudad(string value)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.vAdapter.Connection = cnn;

                    List<CLIENTES> lst = new List<CLIENTES>();
                    foreach (DataSet.V_CLIENTESRow rw in this.vAdapter.GetDataByCiudad(value.Trim()))
                    {
                        lst.Add(GetEntity(rw));
                    }
                    cnn.Close();
                    return lst;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public List<CLIENTES> GetByIdCiudad(decimal value)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.vAdapter.Connection = cnn;

                    List<CLIENTES> lst = new List<CLIENTES>();
                    foreach (DataSet.V_CLIENTESRow rw in this.vAdapter.GetDataByIdCiudad(value))
                    {
                        lst.Add(GetEntity(rw));
                    }
                    cnn.Close();
                    return lst;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public List<CLIENTES> GetByCategoria(string value)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.vAdapter.Connection = cnn;

                    List<CLIENTES> lst = new List<CLIENTES>();
                    foreach (DataSet.V_CLIENTESRow rw in this.vAdapter.GetDataByCategoria(value.Trim()))
                    {
                        lst.Add(GetEntity(rw));
                    }
                    cnn.Close();
                    return lst;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public List<CLIENTES> GetByIdCategoria(decimal value)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.vAdapter.Connection = cnn;

                    List<CLIENTES> lst = new List<CLIENTES>();
                    foreach (DataSet.V_CLIENTESRow rw in this.vAdapter.GetDataByIdCategoria(value))
                    {
                        lst.Add(GetEntity(rw));
                    }
                    cnn.Close();
                    return lst;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        private CLIENTES GetEntity(DataSet.V_CLIENTESRow rw)
        {
            CLIENTES cliente = null;
            if (rw != null)
            {
                cliente = new CLIENTES();

                cliente.ID = rw.ID;
                cliente.CLIENTE = rw.CLIENTE;
                cliente.NOMBRE_COMERCIAL = rw.NOMBRE_COMERCIAL;
                cliente.DIRECCION = rw.DIRECCION;
                cliente.ID_CIUDAD = rw.ID_CIUDAD;
                cliente.CIUDADES = new CIUDADES { ID = rw.ID_CIUDAD, CIUDAD = rw.CIUDAD };
                cliente.ID_VENDEDOR = rw.ID_VENDEDOR;
                cliente.VENDEDORES = new VENDEDORES { ID = rw.ID_VENDEDOR, VENDEDOR = rw.VENDEDOR };
                cliente.RNC = rw.RNC;
                cliente.TELEFONO = rw.TELEFONO;
                if (!rw.IsCELULARNull())
                    cliente.CELULAR = rw.CELULAR;
                if (!rw.IsFAXNull())
                    cliente.FAX = rw.FAX;
                if (!rw.IsOTRONull())
                    cliente.OTRO = rw.OTRO;
                if (!rw.IsEMAILNull())
                    cliente.EMAIL = rw.EMAIL;
                cliente.FECHA = rw.FECHA;
                if (!rw.IsOBSERVACIONESNull())
                    cliente.OBSERVACIONES = rw.OBSERVACIONES;
                if (!rw.IsLIMITE_CREDITONull())
                    cliente.LIMITE_CREDITO = rw.LIMITE_CREDITO;
                cliente.ACTIVO = rw.ACTIVO;
                cliente.ID_CATEGORIA = rw.ID_CATEGORIA;
                cliente.CLIENTES_CAT = new CLIENTES_CAT { ID = rw.ID_CATEGORIA, CATEGORIA = rw.CATEGORIA };
                if (!rw.IsID_ZONANull())
                {
                    cliente.ID_ZONA = rw.ID_ZONA;
                    cliente.ZONAS = new ZONAS { ID = rw.ID_ZONA, ZONA = rw.ZONA };
                }
                if (!rw.IsID_RUTANull())
                {
                    cliente.ID_RUTA = rw.ID_RUTA;
                    cliente.RUTAS = new RUTAS { ID = rw.ID_RUTA, RUTA = rw.RUTA };
                }
                cliente.ID_FORMA_PAGO = rw.ID_FORMA_PAGO;
                cliente.FORMAS_PAGOS = new FORMAS_PAGOS { ID = rw.ID_FORMA_PAGO, FORMA_PAGO = rw.FORMA_PAGO };
                cliente.IMPUESTOS = rw.IMPUESTOS;
                cliente.MONTO_ADEUDADO = rw.MONTO_ADEUDADO;
            }
            return cliente;
        }
    }

    public class ClientesCatDA
    {
        private CLIENTES_CATTableAdapter Adapter = new CLIENTES_CATTableAdapter();
        private DataSet.CLIENTES_CATDataTable entities;
        private DataSet.CLIENTES_CATRow entity;

        public decimal Insert(CLIENTES_CAT value)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    Adapter.Connection = cnn;

                    entities = new DataSet.CLIENTES_CATDataTable();

                    entity = entities.NewCLIENTES_CATRow();

                    entity.ID = value.ID;
                    entity.CATEGORIA = value.CATEGORIA.Trim();

                    entities.AddCLIENTES_CATRow(entity);
                    Adapter.Update(entities);

                    value.ID = entity.ID;
                    return value.ID;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public decimal Update(CLIENTES_CAT value)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.Adapter.Connection = cnn;

                    entities = Adapter.GetDataByID(value.ID);
                    entity = entities.FirstOrDefault();


                    entity.CATEGORIA = value.CATEGORIA.Trim();

                    this.Adapter.Update(entities);

                    return entity.ID;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public CLIENTES_CAT GetById(decimal id)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.Adapter.Connection = cnn;

                    CLIENTES_CAT banco;

                    entity = this.Adapter.GetDataByID(id).FirstOrDefault();

                    if (entity != null)
                    {
                        banco = new CLIENTES_CAT
                        {
                            ID = entity.ID,
                            CATEGORIA = entity.CATEGORIA,
                        };
                    }
                    else
                        banco = null;

                    return banco;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public List<CLIENTES_CAT> GetAll()
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.Adapter.Connection = cnn;

                    List<CLIENTES_CAT> lst = new List<CLIENTES_CAT>();
                    foreach (DataSet.CLIENTES_CATRow rw in this.Adapter.GetData())
                    {
                        lst.Add(new CLIENTES_CAT
                        {
                            ID = rw.ID,
                            CATEGORIA = rw.CATEGORIA,
                        });
                    }
                    cnn.Close();
                    return lst;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }

        public List<CLIENTES_CAT> GetByDescripcion(string value)
        {
            using (SqlConnection cnn = new SqlConnection(Connection.GetConnectionString))
            {
                try
                {
                    cnn.Open();
                    this.Adapter.Connection = cnn;

                    List<CLIENTES_CAT> lst = new List<CLIENTES_CAT>();
                    foreach (DataSet.CLIENTES_CATRow rw in this.Adapter.GetDataByCategoria(value.Trim()))
                    {
                        lst.Add(new CLIENTES_CAT
                        {
                            ID = rw.ID,
                            CATEGORIA = rw.CATEGORIA,
                        });
                    }
                    return lst;
                }
                finally
                {
                    if (cnn.State != System.Data.ConnectionState.Closed)
                        cnn.Close();
                }
            }
        }
    }
}
